﻿using System;
using System.Data;
using System.Text;
using Agile.DataAccess.Providers.Common;
using Agile.DataAccess.Providers.Common.Builders;

namespace Agile.DataAccess
{
    internal class SqlServerProvider : IDbProvider
    {
        public string ProviderName
        {
            get
            {
                return "System.Data.SqlClient";
            }
        }

        public bool SupportsOutputParameters
        {
            get { return true; }
        }

        public bool SupportsMultipleResultsets
        {
            get { return true; }
        }

        public bool SupportsMultipleQueries
        {
            get { return true; }
        }

        public bool SupportsStoredProcedures
        {
            get { return true; }
        }

        public bool RequiresIdentityColumn
        {
            get { return false; }
        }

        public IDbConnection CreateConnection(string connectionString)
        {
            return ConnectionFactory.CreateConnection(ProviderName, connectionString);
        }

        public string GetParameterName(string parameterName)
        {
            return "@" + parameterName;
        }

        public string GetSelectBuilderAlias(string name, string alias)
        {
            return name + " as " + alias;
        }

        public string GetSqlForQueryBuilder(QueryBuilderData data)
        {
            var sql = new StringBuilder();
            if (data.PagingCurrentPage == 1)
            {
                if (data.PagingItemsPerPage == 0)
                    sql.Append("SELECT");
                else
                    sql.Append("SELECT TOP " + data.PagingItemsPerPage.ToString());
                sql.Append(" " + data.Select);
                sql.Append(" FROM " + data.From);
                if (data.WhereSql.Length > 0)
                    sql.Append(" WHERE " + data.WhereSql);
                if (data.GroupBy.Length > 0)
                    sql.Append(" GROUP BY " + data.GroupBy);
                if (data.Having.Length > 0)
                    sql.Append(" HAVING " + data.Having);
                if (data.Orderby.Length > 0)
                    sql.Append(" ORDER BY " + data.Orderby);
                return sql.ToString();
            }
            else
            {
                sql.Append(" FROM " + data.From);
                if (data.WhereSql.Length > 0)
                    sql.Append(" WHERE " + data.WhereSql);
                if (data.GroupBy.Length > 0)
                    sql.Append(" GROUP BY " + data.GroupBy);
                if (data.Having.Length > 0)
                    sql.Append(" HAVING " + data.Having);

                var pagedSql = string.Format(@"WITH PagedPersons as
								(
									SELECT TOP 100 percent {0}, row_number() over (ORDER BY {1}) as FLUENTDATA_ROWNUMBER
									{2}
								)
								SELECT * FROM PagedPersons
								WHERE fluentdata_RowNumber between {3} AND {4}",
                                             data.Select,
                                             data.Orderby,
                                             sql,
                                             data.GetFromItems(),
                                             data.GetToItems());
                return pagedSql;
            }
        }

        public string GetSqlForSelectBuilder(SelectBuilderData data, bool isBuilder)
        {
            var sql = new StringBuilder();
            if (data.PagingCurrentPage == 1)
            {
                sql.Append(data.PagingItemsPerPage == 0 ? "SELECT " : "SELECT TOP " + data.PagingItemsPerPage.ToString() + " ");
                var temp = string.Empty;
                foreach (var column in data.Columns)
                {
                    if (temp.Length > 0)
                        temp += ",";
                    temp += this.EscapeColumnName(column.ColumnName);
                }
                sql.Append(isBuilder ? temp : "* ");
                sql.Append("\r\nFROM ");
                sql.Append(data.ObjectName);

                temp = string.Empty;
                foreach (var column in data.Where)
                {
                    if (temp.Length > 0)
                        temp += " AND ";
                    temp += string.Format("{0} = {1}{2}", this.EscapeColumnName(column.ColumnName), "@", column.ParameterName);
                }
                if (temp.Length > 0)
                    sql.Append("\r\nWHERE " + temp);

                temp = string.Empty;
                foreach (var column in data.OrderBy)
                {
                    if (temp.Length > 0)
                        temp += ",";
                    temp += string.Format("{0} {1}", this.EscapeColumnName(column.ColumnName), column.Value);
                }
                if (temp.Length > 0)
                    sql.Append("\r\nORDER BY " + temp);
                return sql.ToString();
            }
            else
            {
                string filed = string.Empty;
                foreach (var column in data.Columns)
                {
                    if (filed.Length > 0)
                        filed += ",";
                    filed += this.EscapeColumnName(column.ColumnName);
                }
                filed = isBuilder ? filed : "* ";

                sql.Append(" FROM " + data.ObjectName);
                string where = string.Empty;
                foreach (var column in data.Where)
                {
                    if (where.Length > 0)
                        where += " AND ";
                    where += string.Format("{0} = {1}{2}", this.EscapeColumnName(column.ColumnName), "@", column.ParameterName);
                }
                if (where.Length > 0)
                    sql.Append("\r\nWHERE " + where);

                string order = string.Empty;
                foreach (var column in data.OrderBy)
                {
                    if (order.Length > 0)
                        order += ",";
                    order += string.Format("{0} {1}", this.EscapeColumnName(column.ColumnName), column.Value);
                }

                var pagedSql = string.Format(@"WITH PagedPersons as
(
SELECT TOP {5} percent {0}, row_number() over (ORDER BY {1}) as FLUENTDATA_ROWNUMBER {2}
)
SELECT * FROM PagedPersons
WHERE fluentdata_RowNumber between {3} AND {4}",
                                             filed,
                                             order,
                                             sql,
                                             data.GetFromItems(),
                                             data.GetToItems(),
                                             data.PagingItemsPerPage);
                return pagedSql;
            }
        }

        public string GetSqlForInsertBuilder(BuilderData data)
        {
            return new InsertBuilderSqlGenerator().GenerateSql(this, "@", data);
        }

        public string GetSqlForUpdateBuilder(BuilderData data)
        {
            return new UpdateBuilderSqlGenerator().GenerateSql(this, "@", data);
        }

        public string GetSqlForDeleteBuilder(BuilderData data)
        {
            return new DeleteBuilderSqlGenerator().GenerateSql(this, "@", data);
        }

        public string GetSqlForExistsBuilder(BuilderData data)
        {
            string sql = "IF EXISTS (SELECT 1 FROM {0} WHERE {1}) SELECT 1 ELSE SELECT 0";
            return new ExistsBuilderSqlGenerator().GenerateSql(this, "@", data, sql);
        }

        public string GetSqlForStoredProcedureBuilder(BuilderData data)
        {
            return data.ObjectName;
        }

        public string GetSqlForPagingBuilder(SelectBuilderData data, string orderBy)
        {
            var sql = new StringBuilder();
            if (data.PagingCurrentPage == 1)
            {
                sql.Append(data.PagingItemsPerPage == 0 ? "SELECT " : "SELECT TOP " + data.PagingItemsPerPage.ToString());
                sql.Append(" * FROM (\r\n" + data.ObjectName + " ) AS t");
                if (!string.IsNullOrEmpty(orderBy))
                    sql.Append("\r\nORDER BY " + orderBy + "\r\n");
            }
            else
            {
                sql.Append(string.Format(@"WITH PagedPersons as
(
SELECT TOP {0} percent *, row_number() over (ORDER BY {1}) as FLUENTDATA_ROWNUMBER FROM 
    (
{2}
    ) AS t
)
SELECT * FROM PagedPersons
WHERE fluentdata_RowNumber between {3} AND {4}",
                                            data.PagingItemsPerPage,
                                            orderBy,
                                            data.ObjectName,
                                            data.GetFromItems(),
                                            data.GetToItems()));
            }
            return sql.ToString();
        }

        public DataTypes GetDbTypeForClrType(Type clrType)
        {
            return new DbTypeMapper().GetDbTypeForClrType(clrType);
        }

        public object ExecuteReturnLastId<T>(IDbCommand command, string identityColumnName = null)
        {
            if (command.Data.Sql[command.Data.Sql.Length - 1] != ';')
                command.Script(";");

            command.Script("SELECT SCOPE_IDENTITY()");

            object lastId = null;

            command.Data.ExecuteQueryHandler.ExecuteQuery(false, () =>
            {
                lastId = command.Data.InnerCommand.ExecuteScalar();
            });

            return lastId;
        }

        public void OnCommandExecuting(IDbCommand command)
        {
        }

        public string EscapeColumnName(string name)
        {
            if (name.Contains("["))
                return name;
            return "[" + name + "]";
        }
    }
}
